﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using DataAggregationService.Model;
using Microsoft.Extensions.Options;
using MySqlConnector;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Text.RegularExpressions;
using Microsoft.Extensions.FileSystemGlobbing.Internal;

namespace DataAggregationService.Service
{
	public class DbServiceImpl : IDbService
	{
		private readonly ILogger<DbServiceImpl> _logger;
		private readonly SyncConfig _syncConfig;
		private readonly IDictionary<string, DBConfig> _dbConfigDic;
		private readonly IDictionary<string, object> _syncConfigDic;
		private readonly Regex _pattern;

		public DbServiceImpl(ILogger<DbServiceImpl> logger, IOptions<SyncConfig> syncConfig)
		{
			_logger = logger;
			_syncConfig = syncConfig.Value;

			_dbConfigDic = new Dictionary<string, DBConfig>();
			foreach (var db in _syncConfig.SynDatabases)
			{
				if (!_dbConfigDic.ContainsKey(db.Name))
				{
					_dbConfigDic.Add(db.Name, db);
				}
			}

			_syncConfigDic = new Dictionary<string, object>();
			// 使用反射获取 SyncConfig 对象的所有属性
			foreach (PropertyInfo property in typeof(SyncConfig).GetProperties(BindingFlags.Public | BindingFlags.Instance))
			{
				var value = property.GetValue(_syncConfig);
				if (value != null && (value is ValueType || value is string))
				{
					_syncConfigDic[property.Name] = value;
				}
			}
			// 添加特殊占位符
			_syncConfigDic["CurrentTime"] = DateTime.Now;
			_syncConfigDic["CurrentDate"] = DateTime.Now.Date;

			_pattern = new Regex(@"@(\w+)", RegexOptions.Compiled);
		}

		public async Task AggregateDataAsync()
		{
			foreach (var synTable in _syncConfig.SynTables)
			{
				DBConfig sourceDbConfig = GetDBConfig(synTable.SourceDbName);
				DBConfig targetDbConfig = GetDBConfig(synTable.TargetDbName);

				using (var sourceConnection = CreateConnection(sourceDbConfig.Type, sourceDbConfig.ConnectionString))
				{
					await sourceConnection.OpenAsync();

					using (var command = sourceConnection.CreateCommand())
					{
						// 构建查询条件
						var conditions = new List<string>();
						var parameters = new List<DbParameter>();

						foreach (var column in synTable.SynColumns)
						{
							var matchValue = MatchParameter(column.SourceColName);
							if (!string.IsNullOrEmpty(matchValue) && _syncConfigDic.ContainsKey(matchValue.Replace("@", "")))
							{
								bool exists = parameters.Any(p => p.ParameterName.Equals(matchValue));
								if (!exists)
								{
									var parameter = command.CreateParameter();
									parameter.ParameterName = matchValue;
									parameter.Value = _syncConfigDic[matchValue.Replace("@", "")];
									parameters.Add(parameter);
								}
							}
						}

						string lastSyncValue = await GetLastSyncValue(synTable);
						if (!string.IsNullOrEmpty(lastSyncValue))
						{
							conditions.Add($"{synTable.SynField} > @{synTable.SynField}");
							var parameter = command.CreateParameter();
							parameter.ParameterName = $"@{synTable.SynField}";
							//parameter.DbType = GetDbType(synTable.SynField, lastSyncValue);
							parameter.Value = ConvertToType(synTable.SynField, lastSyncValue);
							parameters.Add(parameter);
						}

						if (!string.IsNullOrEmpty(synTable.SourceWhere))
						{
							conditions.Add(synTable.SourceWhere);
						}

						var whereClause = conditions.Count > 0 ? $"WHERE {string.Join(" AND ", conditions)}" : "";

						// 拼接完整的 SELECT 语句
						var query = BuildSelectQuery(synTable, whereClause);

						command.CommandText = query;
						command.Parameters.AddRange(parameters.ToArray());

						string newSyncValue = lastSyncValue;
						using (var reader = await command.ExecuteReaderAsync())
						{
							while (await reader.ReadAsync())
							{
								var syncFieldValue = GetFieldValue(reader, synTable.SynField);
								if (syncFieldValue != null)
								{
									var syncFieldValueStr = syncFieldValue.ToString();
									if (!string.IsNullOrEmpty(syncFieldValueStr))
									{
										newSyncValue = syncFieldValueStr;
									}
								}

								await InsertOrUpdateTargetDatabase(synTable, reader);
							}
						}

						if (!string.IsNullOrEmpty(newSyncValue) && !newSyncValue.Equals(lastSyncValue))
						{
							newSyncValue = FormatSyncFieldValue(newSyncValue, synTable.SynFieldType);
							// 更新同步状态
							await UpdateLastSyncValue(synTable, newSyncValue);
						}
					}
				}
			}
		}

		private async Task InsertOrUpdateTargetDatabase(SynTable synTable, DbDataReader reader)
		{
			DBConfig targetDbConfig = GetDBConfig(synTable.TargetDbName);
			string sourceTable = synTable.SourceTable;
			string targetTable = synTable.TargetTable;

			using (var targetConnection = CreateConnection(targetDbConfig.Type, targetDbConfig.ConnectionString))
			{
				await targetConnection.OpenAsync();

				var keyColumns = synTable.SynColumns.Where(c => c.IsExistCheck).ToList();
				var keyValues = keyColumns.ToDictionary(
					c => c.TargetColName,
					c => GetFieldValue(reader, c.TargetColName)
				);

				var checkQuery = $@"SELECT COUNT(*) 
                FROM {synTable.TargetTable} 
                WHERE {string.Join(" AND ", keyColumns.Select(k => $"{k.TargetColName} = @{k.TargetColName}"))}";

				using (var checkCommand = targetConnection.CreateCommand())
				{
					checkCommand.CommandText = checkQuery;
					foreach (var key in keyColumns)
					{
						var parameter = checkCommand.CreateParameter();
						parameter.ParameterName = $"@{key.TargetColName}";
						parameter.Value = keyValues[key.TargetColName];
						checkCommand.Parameters.Add(parameter);
					}

					var countObj = await checkCommand.ExecuteScalarAsync();
					int count = 0;
					if (countObj != null)
					{
						count = Convert.ToInt32(countObj);
					}

					if (count > 0)
					{
						// Record exists, update it
						var updateColumns = synTable.SynColumns.Where(c => c.IsUpdateable).ToList();
						var updateValues = updateColumns.ToDictionary(
							c => c.TargetColName,
							c => GetFieldValue(reader, c.TargetColName)
						);

						var updateQuery = $@"UPDATE {synTable.TargetTable} 
                        SET {string.Join(", ", updateColumns.Select(c => $"{c.TargetColName} = @{c.TargetColName}"))} 
                        WHERE {string.Join(" AND ", keyColumns.Select(k => $"{k.TargetColName} = @{k.TargetColName}"))}";

						using (var updateCommand = targetConnection.CreateCommand())
						{
							updateCommand.CommandText = updateQuery;
							foreach (var column in updateColumns)
							{
								var parameter = updateCommand.CreateParameter();
								parameter.ParameterName = $"@{column.TargetColName}";
								parameter.Value = updateValues[column.TargetColName];
								updateCommand.Parameters.Add(parameter);
							}
							foreach (var key in keyColumns)
							{
								var parameter = updateCommand.CreateParameter();
								parameter.ParameterName = $"@{key.TargetColName}";
								parameter.Value = keyValues[key.TargetColName];
								updateCommand.Parameters.Add(parameter);
							}

							await updateCommand.ExecuteNonQueryAsync();
						}
					}
					else
					{
						// Record does not exist, insert it
						var insertColumns = synTable.SynColumns.Where(c => c.IsInsertable).ToList();
						var insertValues = insertColumns.ToDictionary(
							c => c.TargetColName,
							c => GetFieldValue(reader, c.TargetColName)
						);

						var insertQuery = $@"INSERT INTO {synTable.TargetTable} ({string.Join(", ", insertColumns.Select(c => c.TargetColName))})
                        VALUES ({string.Join(", ", insertColumns.Select(c => $"@{c.TargetColName}"))})";

						using (var insertCommand = targetConnection.CreateCommand())
						{
							insertCommand.CommandText = insertQuery;
							foreach (var column in insertColumns)
							{
								var parameter = insertCommand.CreateParameter();
								parameter.ParameterName = $"@{column.TargetColName}";
								parameter.Value = insertValues[column.TargetColName];
								insertCommand.Parameters.Add(parameter);
							}

							await insertCommand.ExecuteNonQueryAsync();
						}
					}
				}
			}
		}

		private async Task<string> GetLastSyncValue(SynTable synTable)
		{
			DBConfig targetDbConfig = GetDBConfig(synTable.TargetDbName);
			string sourceTable = synTable.SourceTable;
			string targetTable = synTable.TargetTable;
			using (var targetConnection = CreateConnection(targetDbConfig.Type, targetDbConfig.ConnectionString))
			{
				await targetConnection.OpenAsync();

				string query;
				switch (targetDbConfig.Type.ToLower())
				{
					case "sqlserver":
						query = $@"SELECT ISNULL(last_value, '') AS LastSyncValue 
                           FROM sync_status 
                           WHERE source_table = @SourceTable AND target_table = @TargetTable";
						break;
					case "mysql":
						query = $@"SELECT IFNULL(last_value, '') AS LastSyncValue 
                           FROM sync_status 
                           WHERE source_table = @SourceTable AND target_table = @TargetTable";
						break;
					default:
						throw new NotSupportedException($"Unsupported database type: {targetDbConfig.Type}");
				}

				using (var command = targetConnection.CreateCommand())
				{
					command.CommandText = query;
					var parameter1 = command.CreateParameter();
					parameter1.ParameterName = "@SourceTable";
					parameter1.DbType = DbType.String;
					parameter1.Value = sourceTable;
					command.Parameters.Add(parameter1);

					var parameter2 = command.CreateParameter();
					parameter2.ParameterName = "@TargetTable";
					parameter2.DbType = DbType.String;
					parameter2.Value = targetTable;
					command.Parameters.Add(parameter2);

					var result = await command.ExecuteScalarAsync();
					if (result != null)
					{
						return FormatSyncFieldValue(result.ToString(), synTable.SynFieldType);
					}
				}
			}

			// 如果没有找到记录，插入默认值
			await UpdateLastSyncValue(synTable, "");
			return "";
		}

		private async Task UpdateLastSyncValue(SynTable synTable, string lastSyncValue)
		{
			DBConfig targetDbConfig = GetDBConfig(synTable.TargetDbName);
			string sourceTable = synTable.SourceTable;
			string targetTable = synTable.TargetTable;

			using (var targetConnection = CreateConnection(targetDbConfig.Type, targetDbConfig.ConnectionString))
			{
				await targetConnection.OpenAsync();

				using (var command = targetConnection.CreateCommand())
				{
					// 检查是否存在记录
					command.CommandText = @"SELECT 1 FROM sync_status WHERE source_table = @SourceTable AND target_table = @TargetTable;";

					var parameter1 = command.CreateParameter();
					parameter1.ParameterName = "@SourceTable";
					parameter1.Value = sourceTable;
					command.Parameters.Add(parameter1);

					var parameter2 = command.CreateParameter();
					parameter2.ParameterName = "@TargetTable";
					parameter2.Value = targetTable;
					command.Parameters.Add(parameter2);

					var countObj = await command.ExecuteScalarAsync();
					int count = 0;
					if (countObj != null)
					{
						count = Convert.ToInt32(countObj);
					}
					bool exists = false;
					if (count > 0)
					{
						exists = true;
					}

					if (exists)
					{
						// 存在记录，执行更新
						command.CommandText = @"UPDATE sync_status SET last_value = @LastSyncValue, update_time = @CurrentTime WHERE source_table = @SourceTable AND target_table = @TargetTable;";

						var parameter3 = command.CreateParameter();
						parameter3.ParameterName = "@LastSyncValue";
						parameter3.Value = lastSyncValue;
						command.Parameters.Add(parameter3);

						var parameter4 = command.CreateParameter();
						parameter4.ParameterName = "@CurrentTime";
						parameter4.Value = DateTime.Now;
						command.Parameters.Add(parameter4);
					}
					else
					{
						// 不存在记录，执行插入
						command.CommandText = @"INSERT INTO sync_status (source_table, target_table, last_value, create_time) VALUES (@SourceTable, @TargetTable, @LastSyncValue, @CurrentTime);";

						var parameter3 = command.CreateParameter();
						parameter3.ParameterName = "@LastSyncValue";
						parameter3.Value = lastSyncValue;
						command.Parameters.Add(parameter3);

						var parameter4 = command.CreateParameter();
						parameter4.ParameterName = "@CurrentTime";
						parameter4.Value = DateTime.Now;
						command.Parameters.Add(parameter4);
					}

					await command.ExecuteNonQueryAsync();
				}
			}
		}

		private DBConfig GetDBConfig(string dbName)
		{
			if (!_dbConfigDic.ContainsKey(dbName))
			{
				throw new ArgumentException($"未找到数据库名为 {dbName} 的配置");
			}
			return _dbConfigDic[dbName];
		}

		private DbConnection CreateConnection(string dbTypeStr, string connectionString)
		{
			switch (dbTypeStr.ToLower())
			{
				case "sqlserver":
					return new SqlConnection(connectionString);
				case "mysql":
					return new MySqlConnection(connectionString);
				case "access":
					return new OleDbConnection(connectionString);
				default:
					throw new NotSupportedException($"Unsupported database type: {dbTypeStr}");
			}
		}

		private string BuildSelectQuery(SynTable synTable, string whereClause)
		{
			var columns = string.Join(", ", synTable.SynColumns.Select(c => c.SourceColName));
			var synFieldColumn = $"{synTable.SynField} AS SyncField";
			var orderByClause = $"ORDER BY {synTable.SynField} ASC";

			DBConfig sourceDbConfig = GetDBConfig(synTable.SourceDbName);
			switch (sourceDbConfig.Type.ToLower())
			{
				case "sqlserver":
					return $@"SELECT TOP({_syncConfig.SelectAmount}) {columns}, {synFieldColumn} 
                          FROM {synTable.SourceTable} {whereClause} {orderByClause}";
				case "mysql":
					return $@"SELECT {columns}, {synFieldColumn} 
                          FROM {synTable.SourceTable} {whereClause} {orderByClause} 
                          LIMIT {_syncConfig.SelectAmount}";
				case "access":
					// OleDb 可能需要特定的处理，这里假设是 Access 数据库
					return $@"SELECT TOP {_syncConfig.SelectAmount} {columns}, {synFieldColumn} 
                          FROM {synTable.SourceTable} {whereClause} {orderByClause}";
				default:
					throw new NotSupportedException($"Unsupported database type: {sourceDbConfig.Type}");
			}
		}

		private object? GetFieldValue(IDataRecord reader, string columnName)
		{
			int ordinal = reader.GetOrdinal(columnName);
			if (reader.IsDBNull(ordinal))
			{
				return null;
			}

			Type fieldType = reader.GetFieldType(ordinal);

			switch (fieldType.Name)
			{
				case "Int32":
					return reader.GetInt32(ordinal);
				case "Int64":
					return reader.GetInt64(ordinal);
				case "Double":
					return reader.GetDouble(ordinal);
				case "Decimal":
					return reader.GetDecimal(ordinal);
				case "Boolean":
					return reader.GetBoolean(ordinal);
				case "DateTime":
					return reader.GetDateTime(ordinal);
				case "Guid":
					return reader.GetGuid(ordinal);
				case "String":
					return reader.GetString(ordinal);
				default:
					return reader.GetString(ordinal);
			}
		}

		private string MatchParameter(string columnName)
		{
			Match match = _pattern.Match(columnName);
			return match.Success ? match.Value : "";
		}

		private DbType GetDbType(string columnName, string value)
		{
			if (DateTime.TryParse(value, out _))
			{
				return DbType.DateTime;
			}
			else if (decimal.TryParse(value, out _))
			{
				return DbType.Decimal;
			}
			else if (int.TryParse(value, out _))
			{
				return DbType.Int32;
			}
			else if (long.TryParse(value, out _))
			{
				return DbType.Int64;
			}
			else
			{
				return DbType.String;
			}
		}

		private object ConvertToType(string columnName, string value)
		{
			if (DateTime.TryParse(value, out var dateTime))
			{
				return dateTime;
			}
			else if (decimal.TryParse(value, out var decimalValue))
			{
				return decimalValue;
			}
			else if (int.TryParse(value, out var intValue))
			{
				return intValue;
			}
			else if (long.TryParse(value, out var longValue))
			{
				return longValue;
			}
			else
			{
				return value;
			}
		}

		private string FormatSyncFieldValue(string syncFieldValue, string synFieldType)
		{
			if (string.IsNullOrEmpty(syncFieldValue) || string.IsNullOrEmpty(synFieldType))
			{
				return syncFieldValue;
			}

			string result = syncFieldValue;
			if (DateTime.TryParse(syncFieldValue, out DateTime dateTime))
			{
				if ("date".Equals(synFieldType.ToLower()))
				{
					result = dateTime.Date.ToString("yyyy-MM-dd");
				}
				if ("datetime".Equals(synFieldType.ToLower()))
				{
					result = dateTime.Date.ToString("yyyy-MM-dd HH:mm:ss");
				}
			}
			else if (decimal.TryParse(syncFieldValue, out var decimalValue))
			{
				result = decimalValue.ToString();
			}
			else if (int.TryParse(syncFieldValue, out var intValue))
			{
				result = intValue.ToString();
			}
			else if (long.TryParse(syncFieldValue, out var longValue))
			{
				result = longValue.ToString();
			}
			return result;
		}
	}
}
